package com.webull.information.center.carwler.dao;

import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import com.webull.framework.util.UtilJdbc;
import com.webull.information.center.carwler.common.model.BaseNewsRel;
import com.webull.information.center.carwler.common.model.BranchNews;
import com.webull.information.center.dao.JdbcHelper;

/**
 *
 */
@Repository
public class CarwlerNewsDao {
    protected final Logger logger = LogManager.getLogger(getClass());
    private static final RowMapper<BaseNewsRel> baseRel = BeanPropertyRowMapper.newInstance(BaseNewsRel.class);
    private static final RowMapper<BranchNews> branchNews = BeanPropertyRowMapper.newInstance(BranchNews.class);


    private @Autowired
    @Qualifier("carwler_jdbcTemplate")
    JdbcTemplate jdbcTemplate;
    private @Autowired
    @Qualifier("carwler_npJdbcTemplate")
    NamedParameterJdbcTemplate npjdbcTemplate;

    /*
     给已入库的新闻增加关联关系
     */
    public Integer addExistNewsRel(String tabName, BaseNewsRel rel) {
        String exSql = StringUtils.replaceOnce(Sql.EXISTExistNewsRel, "{tabName}", tabName).intern();
        logger.trace("addExistNewsRel exSql:{}", exSql);
        int count = npjdbcTemplate.queryForObject(exSql, JdbcHelper.getSqlParameterSource(rel), Integer.class);
        if (count <= 0) {
            String sql = StringUtils.replaceOnce(Sql.ADDExistNewsRel, "{tabName}", tabName).intern();
            logger.trace("addExistNewsRel sql:{}", sql);
            return UtilJdbc.insertAndReturnIntKey(npjdbcTemplate, sql, rel);
        }
        return 0;
    }

    /**
     * 新增新闻
     *
     * @param news
     * @return
     * @since 0.1.0
     */
    public Integer addBranchNews(String tab, BranchNews news) {
        String sql = StringUtils.replaceOnce(Sql.ADD_BRANCH_NEWS, "{tabName}", tab).intern();
        Integer branchId = UtilJdbc.insertAndReturnIntKey(npjdbcTemplate, sql, news);
        return branchId;
    }

    public List<BranchNews> searchBranchNewsByMd5(String tab, String urlMd5) {
        String sql = StringUtils.replaceOnce(Sql.SEARCHBRANCHNEWSMd5, "{tabName}", tab).intern();
        return jdbcTemplate.query(sql, branchNews, urlMd5);
    }

	public List<BranchNews> searchBranchNewsBySumMd5(String tab, String sumMd5) {
		String sql = StringUtils.replaceOnce(Sql.SEARCHBRANCHNEWSSumMd5, "{tabName}", tab).intern();
		return jdbcTemplate.query(sql, branchNews, sumMd5);
	}

    public List<BranchNews> searchBranchNews0(String tab, Integer minId) {
        String sql = StringUtils.replaceOnce(Sql.SEARCHBRANCHNEWRELS, "{tabName}", tab).intern();
        return jdbcTemplate.query(sql, branchNews, minId);
    }

    public List<BaseNewsRel> searchBaseRel0(String tab, Integer minId) {
        String sql = StringUtils.replaceOnce(Sql.SEARCHBRANCHNEWRELS, "{tabName}", tab).intern();
        return jdbcTemplate.query(sql, baseRel, minId);
    }


    /**
     * 清除临时表记录
     * <保留一条record待观察></>
     *
     * @param tab
     * @param id
     * @return
     */
    public List<Integer> selectCarwlerRecord(String tab, Integer id) {
        String sql = StringUtils.replaceOnce(Sql.SELECTBranDRECORD, "{tabName}", tab).intern();
        return jdbcTemplate.queryForList(sql, Integer.class, id);
    }

    public int deleteCarwlerRecord(String tab, Integer id) {
        String sql = StringUtils.replaceOnce(Sql.DeleteBranDRECORD, "{tabName}", tab).intern();
        return jdbcTemplate.update(sql, id);
    }


    private static class Sql {
        private final static String ADDExistNewsRel = "INSERT INTO  {tabName} (news_id, target_type, target_id, base_state) VALUES (:newsId, :targetType, :targetId, 0)";
        private final static String EXISTExistNewsRel = "select count(1) from  {tabName} where news_id = :newsId and  target_id = :targetId and target_type = :targetType and del = 0";
        private static final String ADD_BRANCH_NEWS = "INSERT INTO  {tabName} (title, summary,sum_md5,collect_source, store_shard,store_format,main_pic, news_time, source_name, url,language, region_id,url_md5,store_version,home_page,sync_us,site_type,target_id,target_type,base_state,target_md5) VALUES (:title,:summary,:sumMd5,:collectSource,:storeShard,:storeFormat,:mainPic, :newsTime, :sourceName, :url,:language,:regionId,:urlMd5,:storeVersion,:homePage,:syncUs,:siteType,:targetId,:targetType,0,:targetMd5)";
        private final static String SEARCHBRANCHNEWSMd5 = "select * from  {tabName} where  url_md5 = ?  and del = 0  order by id desc limit 10";

		private final static String SEARCHBRANCHNEWSSumMd5 = "select * from  {tabName} where  sum_md5 = ?  and del = 0  order by id desc limit 10";

        private final static String SEARCHBRANCHNEWRELS = "select * from  {tabName} where base_state = 0 and del = 0  and id > ? order by id asc limit 100";

        private final static String SELECTBranDRECORD = "select id from  {tabName} where id < ? limit 100";
        private final static String DeleteBranDRECORD = "delete from  {tabName} where id = ?";

    }
}
